﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using NS.DAL.Systems;

namespace DAL
{
    public class CommonBase
    {
        /// <summary>
        /// 获取分页数据
        /// </summary>
        /// <param name="SQLString">数据源SQL</param>
        /// <param name="PKey">主键</param>
        /// <param name="OrderName">排序字段</param>
        /// <param name="PageSize">每页数量</param>
        /// <param name="CurrentIndex">当前页码</param>
        /// <param name="TotalPage">总页码</param>
        /// <returns></returns>
        public static DataTable GetDataByPage(string SQLString, string PKey, string OrderName, int PageSize, int CurrentIndex, out int TotalPage)
        {
            DataTable resultData = null;
            int TotalRows = 0;
            TotalPage = 0;
            string strSqlTmp = "select count(" + PKey + ") as RECORDCOUNT from (" + SQLString + ") AAA";
            try
            {
                string results = SQLHelper.ExecuteScalar(strSqlTmp, null);
                if (!string.IsNullOrEmpty(results))
                {
                    TotalRows = Convert.ToInt32(results);
                }
                else
                {
                    return null;
                }

                if (TotalRows % PageSize != 0)
                    TotalPage = TotalRows / PageSize + 1;
                else
                    TotalPage = TotalRows / PageSize;

                string sql;
                if (OrderName.Contains(PKey))
                {
                    sql = "SELECT TOP " + PageSize + " * FROM (" + SQLString + ") AS AAA WHERE " + PKey + " NOT IN(SELECT TOP (" + PageSize + "*(" + CurrentIndex + "-1)) " + PKey + " FROM (" + SQLString + ") AS AAA ORDER BY " + OrderName + " ) ORDER BY " + OrderName;
                }
                else
                {
                    sql = "SELECT TOP " + PageSize + " * FROM (" + SQLString + ") AS AAA WHERE " + PKey + " NOT IN(SELECT TOP (" + PageSize + "*(" + CurrentIndex + "-1)) " + PKey + " FROM (" + SQLString + ") AS AAA ORDER BY " + OrderName + "," + PKey + " ) ORDER BY " + OrderName + "," + PKey;
                }
                resultData = SQLHelper.ExecuteDataSet(sql, null).Tables[0];
            }
            catch (Exception ex)
            {
                throw ex;
            }

            return resultData;
        }
    }
}
